package py.util;

import java.io.File;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;

public class ToolUtil {

	
	public static XSSFCell[] getCellsOfRow(XSSFRow row)
	{
		XSSFCell[] Cells = new XSSFCell[row.getLastCellNum()] ;
		for(int i=0;i<Cells.length;i++)
		{
			Cells[i]=row.getCell(i);
		}
//		Iterator<Cell>  cellIt=row.cellIterator();
//		int colNum=0;
//		while(cellIt.hasNext())
//		{
//			Cells[colNum]=(XSSFCell)cellIt.next();
//			colNum++;
//		}
		return Cells;
	}
	
	public static HSSFCell[] getCellsOfRow(HSSFRow row)
	{
		HSSFCell[] Cells = new HSSFCell[row.getLastCellNum()] ;
		for(int i=0;i<Cells.length;i++)
		{
			Cells[i]=row.getCell(i);
		}
//		Iterator<Cell>  cellIt=row.cellIterator();
//		int colNum=0;
//		while(cellIt.hasNext())
//		{
//			Cells[colNum]=(HSSFCell)cellIt.next();
//			colNum++;
//		}
		return Cells;
	}
	/**
	 * 
	 * @param cell
	 * @return
	 */
	private static String getNumberValue(Cell cell)
	{
		String cellValue;
		String fvalue=cell.getCellStyle().getDataFormatString(); //获取格式
    	int dataFormat=cell.getCellStyle().getDataFormat();
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
            
            if(dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58)
            {
            	sdf = new SimpleDateFormat("yyyy-MM-dd");  
            }
            else if(dataFormat == 20 || dataFormat == 32)
            {
            	sdf = new SimpleDateFormat("HH:mm:ss"); 
            }
            else if(fvalue.indexOf("/")>0||fvalue.indexOf("-")>0||fvalue.indexOf("月")>0)
            {
            	sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
            }
            else if(fvalue.indexOf(":")>0)
            {
            	sdf = new SimpleDateFormat("HH:mm:ss"); 
            }
            Date date = cell.getDateCellValue();  
            cellValue = sdf.format(date);  
        } else if (cell.getCellStyle().getDataFormat() == 58) {  
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
//            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
            double value = cell.getNumericCellValue();  
            Date date = org.apache.poi.ss.usermodel.DateUtil  
                    .getJavaDate(value);  
            cellValue = sdf.format(date);  
        } else {  
        	if(fvalue.indexOf("/")>0||fvalue.indexOf("-")>0||fvalue.indexOf("月")>0)
            {
            	sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
            	double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil  
                        .getJavaDate(value);  
                cellValue = sdf.format(date);
            }
            else if(fvalue.indexOf(":")>0)
            {
            	sdf = new SimpleDateFormat("HH:mm:ss"); 
            	double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil  
                        .getJavaDate(value);  
                cellValue = sdf.format(date);
            }
            else
            {
            	double value = cell.getNumericCellValue();  
                CellStyle style = cell.getCellStyle();  
                DecimalFormat format = new DecimalFormat("#.##");  
//                String temp = style.getDataFormatString();  
                // 单元格设置成常规  
//                if (temp.equals("General")) {  
//                    format.applyPattern("#");  
//                }  
                cellValue = format.format(value);
            }
              
        }
    	return cellValue;
	}
	
	public static String getCellValue(XSSFCell cell) {  
		if(cell==null)
			return "";
        String cellValue = "";  
//        DecimalFormat df = new DecimalFormat("#");  
        switch (cell.getCellType()) {  
        case XSSFCell.CELL_TYPE_STRING:  
            cellValue = cell.getRichStringCellValue().getString().trim();  
            break;  
        case XSSFCell.CELL_TYPE_NUMERIC:  
        	cellValue=getNumberValue(cell);
            break; 
//            cellValue = df.format(cell.getNumericCellValue()).toString();  
//            break;  
        case XSSFCell.CELL_TYPE_BOOLEAN:  
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();  
            break;  
        case XSSFCell.CELL_TYPE_FORMULA:  
//            cellValue = cell.getCellFormula();  
            try {  
            	double value = cell.getNumericCellValue();  
                CellStyle style = cell.getCellStyle();  
                DecimalFormat format = new DecimalFormat("#.##");
                cellValue = format.format(value); 
//            	cellValue = String.valueOf(cell.getNumericCellValue());  
           } catch (IllegalStateException e) {  
        	   cellValue = String.valueOf(cell.getRichStringCellValue());  
           } 
            break;  
        default:  
            cellValue = "";  
        }  
        return cellValue;  
    } 
	public static String getCellValue(HSSFCell cell) {  
		if(cell==null)
			return "";
        String cellValue = "";  
//        DecimalFormat df = new DecimalFormat("#");  
        switch (cell.getCellType()) {  
        case HSSFCell.CELL_TYPE_STRING:  
            cellValue = cell.getRichStringCellValue().getString().trim();  
            break;  
        case HSSFCell.CELL_TYPE_NUMERIC: 
        	cellValue=getNumberValue(cell);
            break;  
  
        case HSSFCell.CELL_TYPE_BOOLEAN:  
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();  
            break;  
        case HSSFCell.CELL_TYPE_FORMULA:  
//            cellValue = cell.getCellFormula();  
            try {  
            	double value = cell.getNumericCellValue();  
                CellStyle style = cell.getCellStyle();  
                DecimalFormat format = new DecimalFormat("0");
                cellValue = format.format(value); 
//            	cellValue = String.valueOf(cell.getNumericCellValue());  
           } catch (IllegalStateException e) {  
        	   cellValue = String.valueOf(cell.getRichStringCellValue());  
           } 
            break;  
        default:  
            cellValue = "";  
        }  
        return cellValue;  
    }

}
